package db_utils

import (
	"bytes"
	"dgo/goutils/utils"
	"fmt"
	"strings"
	"time"
)

const (
	ACTION_UPDATE int = 1
	ACTION_APPEND int = 2
	ACTION_DELETE int = 3
)

/*
update:

	2023-10-19 添加设置oldvalue， where语句优先从olds中获取
*/
type RecUpdateUpbuilder struct {
	KeyFields                string // 分号分割
	Uptablename              string
	ignoreUpFields           string // 分号分割
	ingoreInsertFields       string // 分号分割
	FieldPrefix, FieldSuffix string
	fieldTypeMap             map[string]int // 字段类型   0:未知, 1:字符串, 2:日期
	valsMap                  map[string]interface{}
	oldValsMap               map[string]interface{}
	BeforeBuildFunc          func(sender *RecUpdateUpbuilder, action int) bool
	OnSetValue               func(key *string, val *interface{})
	OnConvertSQLValueFunc    func(key *string, val *interface{}) bool // 设置字段类型, 降低效率, 转换字段值到sql的时候调用
	DbType                   string
}

func EscapeMSSQLStr(str string) string {
	var buf bytes.Buffer
	for _, runedata := range str {
		switch runedata {
		default:
			buf.WriteRune(runedata)
		}
	}
	return string(buf.Bytes())
}

func EscapeMySQLStr(str string) string {
	var buf bytes.Buffer
	for _, runedata := range str {
		switch runedata {
		case '\'':
			buf.WriteByte('\'')
			buf.WriteByte('\'')
		case '\\':
			buf.WriteByte('\\')
			buf.WriteByte('\\')

		default:
			buf.WriteRune(runedata)
		}
	}
	return string(buf.Bytes())
}

func EscapeSQLStr(str string, dbtype string) string {
	if dbtype == "mssql" {
		return EscapeMSSQLStr(str)
	} else {
		return EscapeMySQLStr(str)
	}

}

func ToSQLValEx(val interface{}, dbtype string) string {
	switch s := val.(type) {
	case time.Time:
		return fmt.Sprintf("'%s'", utils.DateTimeString(s))
	case string:
		return fmt.Sprintf("'%s'", EscapeSQLStr(s, dbtype))
	case int64:
		return fmt.Sprintf("%d", s)
	case int:
		return fmt.Sprintf("%d", s)
	case int32:
		return fmt.Sprintf("'%d'", s)
	case int16:
		return fmt.Sprintf("'%d'", s)
	case int8:
		return fmt.Sprintf("%d", s)
	case float32, float64:
		return fmt.Sprintf("%f", s)
	case bool:
		if s {
			return "1"
		} else {
			return "0"
		}
	case []byte:
		return fmt.Sprintf("0x%s", utils.BufToHexStr(s, 0, ""))
	case nil:
		return "NULL"
	default:
		return fmt.Sprintf("'%v'", s)
	}
}

func ToSQLVal(val interface{}) string {
	switch s := val.(type) {
	case time.Time:
		return fmt.Sprintf("'%s'", utils.DateTimeString(s))
	case string:
		return fmt.Sprintf("'%s'", utils.EscapeSQLStr(s))
	case int64:
		return fmt.Sprintf("%d", s)
	case int:
		return fmt.Sprintf("%d", s)
	case int32:
		return fmt.Sprintf("'%d'", s)
	case int16:
		return fmt.Sprintf("'%d'", s)
	case int8:
		return fmt.Sprintf("%d", s)
	case float32, float64:
		return fmt.Sprintf("%f", s)
	case bool:
		if s {
			return "1"
		} else {
			return "0"
		}
	case []byte:
		return fmt.Sprintf("0x%s", utils.BufToHexStr(s, 0, ""))
	case nil:
		return "NULL"
	default:
		return fmt.Sprintf("'%v'", s)
	}
}

func NewRecUpdateUpbuilder() *RecUpdateUpbuilder {
	rval := &RecUpdateUpbuilder{valsMap: make(map[string]interface{}), oldValsMap: make(map[string]interface{})}
	rval.Clear()
	return rval
}

func (this *RecUpdateUpbuilder) Clear() {
	for k, _ := range this.valsMap {
		delete(this.valsMap, k)
	}
	for k, _ := range this.oldValsMap {
		delete(this.oldValsMap, k)
	}
}

func (this *RecUpdateUpbuilder) Count() int {
	return len(this.valsMap)
}

/*
*

	用分号分割
*/
func (this *RecUpdateUpbuilder) SetIgnoreUpdateFields(ignorefields string) {
	if !strings.HasSuffix(ignorefields, ";") {
		this.ignoreUpFields = ignorefields + ";"
	} else {
		this.ignoreUpFields = ignorefields
	}
}

/*
*

	用分号分割
*/
func (this *RecUpdateUpbuilder) SetIgnoreInsertFields(val string) {
	if !strings.HasSuffix(val, ";") {
		this.ingoreInsertFields = val + ";"
	} else {
		this.ingoreInsertFields = val
	}
}

func (this *RecUpdateUpbuilder) SetOldValue(fieldname string, val interface{}) {
	if this.OnSetValue != nil {
		this.OnSetValue(&fieldname, &val)
	}
	if val == "$now$" {
		this.oldValsMap[fieldname] = time.Now()
	} else {
		this.oldValsMap[fieldname] = val
	}
}

func (this *RecUpdateUpbuilder) SetValue(fieldname string, val interface{}) {
	if this.OnSetValue != nil {
		this.OnSetValue(&fieldname, &val)
	}
	if val == "$now$" {
		this.valsMap[fieldname] = time.Now()
	} else {
		this.valsMap[fieldname] = val
	}
}

func (this *RecUpdateUpbuilder) SetFieldsAnsStrValues(fields []string, values []string) *RecUpdateUpbuilder {
	l := len(fields)
	if l%2 != 0 { // 长度必须是双数
		panic("values长度必须大于或者等于fields长度")
	}
	for i := 0; i < l; i++ {
		fieldname := fields[i]
		var v1 interface{} = values[i]
		if this.OnSetValue != nil {
			this.OnSetValue(&fieldname, &v1)
		}
		this.valsMap[fieldname] = v1
	}
	return this
}

func (this *RecUpdateUpbuilder) SetFieldsAnsStrValuesEx(fields []string, values []string, cb func(fieldname, val *string) bool) *RecUpdateUpbuilder {
	l := len(fields)
	if len(values) < l {
		panic("values长度必须大于或者等于fields长度")
	}
	for i := 0; i < l; i++ {
		if len(fields[i]) == 0 {
			continue
		}
		if cb != nil {
			fieldname := fields[i]
			val := values[i]
			if cb(&fieldname, &val) {
				var v1 interface{} = val
				if this.OnSetValue != nil {
					this.OnSetValue(&fieldname, &v1)
				}
				this.valsMap[fieldname] = v1
			}
		} else {
			fieldname := fields[i]
			var v1 interface{} = values[i]
			if this.OnSetValue != nil {
				this.OnSetValue(&fieldname, &v1)
			}
			this.valsMap[fieldname] = values[i]
		}

	}
	return this
}

func (this *RecUpdateUpbuilder) SetFieldsAnsValues(fields []string, values ...interface{}) *RecUpdateUpbuilder {
	l := len(fields)
	if l%2 != 0 { // 长度必须是双数
		panic("values长度必须大于或者等于fields长度")
	}
	for i := 0; i < l; i++ {
		fieldname := fields[i]
		var v1 interface{} = values[i]
		if this.OnSetValue != nil {
			this.OnSetValue(&fieldname, &v1)
		}
		this.valsMap[fieldname] = v1
	}
	return this
}

func (this *RecUpdateUpbuilder) RemoveFields(fields string) int {
	if len(fields) == 0 {
		return 0
	}

	n := 0
	utils.RangeStringSep2(fields, func(idx int, s1 string) bool {
		if len(s1) > 0 {
			delete(this.valsMap, s1)
			n++
		}
		return true
	}, ',', ';')
	return n
}

func (this *RecUpdateUpbuilder) Remove(key string) *RecUpdateUpbuilder {
	delete(this.valsMap, key)
	return this
}

func (this *RecUpdateUpbuilder) SetValues(kvPairs ...interface{}) *RecUpdateUpbuilder {
	l := len(kvPairs)
	if l%2 != 0 { // 长度必须是双数
		panic("kv Pairs 长度必须是双数")
	}
	i := 0
	for i < l {
		if key, ok := kvPairs[i].(string); ok {
			var v1 interface{} = kvPairs[i+1]
			if this.OnSetValue != nil {
				this.OnSetValue(&key, &v1)
			}
			this.valsMap[key] = v1
		}
		i += 2
	}

	return this

}

func (this *RecUpdateUpbuilder) BuildWhereWithAll() string {
	rval := ""
	j := 0
	for k, v := range this.valsMap {
		if j > 0 {
			rval += " AND "
		}
		if oldv, ok := this.oldValsMap[k]; ok {
			v = oldv
		}

		if v == nil {
			rval += fmt.Sprintf("%s IS NULL", this.FieldSQLV(k))
		} else {
			rval += fmt.Sprintf("%s=%s", this.FieldSQLV(k), ToSQLValEx(v, this.DbType))
		}
		j++
	}

	return rval
}

func (this *RecUpdateUpbuilder) buildWhere(keyfields []string) string {
	rval := ""
	j := 0
	for _, k := range keyfields {
		v, ok := this.oldValsMap[k]
		if !ok {
			v, ok = this.valsMap[k]
		}
		if ok {
			if j > 0 {
				rval += " AND "
			}

			if this.OnConvertSQLValueFunc != nil && this.OnConvertSQLValueFunc(&k, &v) {
				if v == nil {
					rval += fmt.Sprintf("%s IS NULL", this.FieldSQLV(k))
				} else {
					rval += fmt.Sprintf("%s=%v", this.FieldSQLV(k), v)
				}
			} else {
				if v == nil {
					rval += fmt.Sprintf("%s IS NULL", this.FieldSQLV(k))
				} else {
					rval += fmt.Sprintf("%s=%s", this.FieldSQLV(k), ToSQLValEx(v, this.DbType))
				}
			}

			j++
		}
	}

	return rval
}

func (this *RecUpdateUpbuilder) BuildExists(seleFields, tablename, keyfields string) string {
	return fmt.Sprintf("select %s from %s where %s", seleFields, tablename, this.BuildWhere(keyfields))
}

func (this *RecUpdateUpbuilder) BuildDelete(tableName string, keyfields string) string {
	if this.BeforeBuildFunc != nil {
		this.BeforeBuildFunc(this, ACTION_DELETE)
	}
	var strWhere string
	if len(keyfields) == 0 {
		strWhere = this.BuildWhereWithAll()
	} else {
		strWhere = this.BuildWhere(keyfields)
	}

	if len(strWhere) == 0 {
		return ""
	}
	strUpdate := fmt.Sprintf("DELETE FROM %s ", tableName)

	return strUpdate + " WHERE " + strWhere
}

func (this *RecUpdateUpbuilder) BuildWhere(keyfields string) string {
	var keys []string
	if strings.Contains(keyfields, ";") {
		keys = strings.Split(keyfields, ";")
	} else {
		keys = strings.Split(keyfields, ",")
	}
	strWhere := this.buildWhere(keys)
	return strWhere
}

func (this *RecUpdateUpbuilder) FieldSQLV(fieldname string) string {
	return fmt.Sprintf("%s%s%s", this.FieldPrefix, fieldname, this.FieldSuffix)
}

func (this *RecUpdateUpbuilder) BuildUpdateEx() string {
	return this.BuildUpdate(this.Uptablename, this.KeyFields)
}

func (this *RecUpdateUpbuilder) Exists(keyfield string) bool {
	return this.valsMap[keyfield] != nil
}

func (this *RecUpdateUpbuilder) hasOldValue(keyfield string) bool {
	_, ok := this.oldValsMap[keyfield]
	return ok
}

//	func (this *RecUpdateUpbuilder) BuildExists(tableName string, keyfields string) string {
//		var keys []string
//		if strings.Contains(keyfields, ";") {
//			keys = strings.Split(keyfields, ";")
//		} else {
//			keys = strings.Split(keyfields, ",")
//		}
//		strWhere := this.buildWhere(keys)
//
//		return fmt.Sprintf("SELECT 1 FROM %s WHERE %s", tableName, strWhere)
//
// }
func (this *RecUpdateUpbuilder) BuildUpdate(tableName string, keyfields string) string {
	if this.BeforeBuildFunc != nil {
		this.BeforeBuildFunc(this, ACTION_UPDATE)
	}

	var keys []string
	if strings.Contains(keyfields, ";") {
		keys = strings.Split(keyfields, ";")
	} else {
		keys = strings.Split(keyfields, ",")
	}
	strWhere := this.buildWhere(keys)

	if len(strWhere) == 0 {
		return ""
	}
	strUpdate := fmt.Sprintf("UPDATE %s SET ", tableName)
	j := 0
	for k, v := range this.valsMap {
		if utils.SliceIndex(keys, k) == -1 || this.hasOldValue(k) {
			if len(this.ignoreUpFields) > 0 {
				idx := strings.Index(this.ignoreUpFields, k+";")
				if idx >= 0 {
					continue
				}
			}

			if j > 0 {
				strUpdate += ","
			}
			if this.OnConvertSQLValueFunc != nil && this.OnConvertSQLValueFunc(&k, &v) {
				strUpdate += fmt.Sprintf("%s=%v", this.FieldSQLV(k), v)
			} else {
				strUpdate += fmt.Sprintf("%s=%s", this.FieldSQLV(k), ToSQLValEx(v, this.DbType))
			}

			j++
		}
	}

	if j == 0 {
		return ""
	}

	return strUpdate + " WHERE " + strWhere

}

func (this *RecUpdateUpbuilder) BuildInsert(tableName string) string {
	if this.BeforeBuildFunc != nil {
		this.BeforeBuildFunc(this, ACTION_APPEND)
	}
	str := fmt.Sprintf("INSERT INTO %s (", tableName)
	strVals := " VALUES("
	j := 0
	for k, v := range this.valsMap {
		if len(this.ingoreInsertFields) > 0 {
			if strings.Index(this.ingoreInsertFields, k+";") != -1 {
				continue
			}
		}

		if j > 0 {
			str += ","
			strVals += ","
		}
		if this.OnConvertSQLValueFunc != nil {
			if this.OnConvertSQLValueFunc(&k, &v) {
				str += this.FieldSQLV(k)
				strVals += fmt.Sprintf("%v", v)
			} else {
				str += this.FieldSQLV(k)
				strVals += ToSQLValEx(v, this.DbType)
			}
		} else {
			str += this.FieldSQLV(k)
			strVals += ToSQLValEx(v, this.DbType)
		}
		j++
	}
	str += ")" + strVals + ")"
	return str
}
